﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;

namespace RobotCoder
{
    /// <summary>
    /// Provides utility functions for the data tier generator.
    /// </summary>
    internal static class Utility
    {
        /// <summary>
        /// Creates the specified sub-directory, if it doesn't exist.
        /// </summary>
        /// <param name="name">The name of the sub-directory to be created.</param>
        public static void CreateSubDirectory(string name)
        {
            if (Directory.Exists(name) == false)
            {
                Directory.CreateDirectory(name);
            }
        }

        /// <summary>
        /// Creates the specified sub-directory, if it doesn't exist.
        /// </summary>
        /// <param name="name">The name of the sub-directory to be created.</param>
        /// <param name="deleteIfExists">Indicates if the directory should be deleted if it exists.</param>
        public static void CreateSubDirectory(string name, bool deleteIfExists)
        {
            if (Directory.Exists(name))
            {
                Directory.Delete(name, true);
            }

            Directory.CreateDirectory(name);
        }

        /// <summary>
        /// Delete folder
        /// </summary>
        /// <param name="name"></param>
        public static void DeleteSubDirectory(string name)
        {
            try
            {
                if (System.IO.Directory.Exists(name))
                {
                    DirectoryInfo objDirInfo = new DirectoryInfo(name);

                    //reset attribute before deleting
                    SetFolderAttr(objDirInfo, FileAttributes.Normal);
                    objDirInfo.Delete(true);
                }
            }
            catch { }
        }

        /// <summary>
        /// Set folder's attribute
        /// </summary>
        /// <param name="objDir"></param>
        /// <param name="emAttr"></param>
        public static void SetFolderAttr(System.IO.DirectoryInfo objDir, FileAttributes emAttr)
        {
            try
            {
                if (objDir.Exists)
                {
                    objDir.Attributes = emAttr;

                    foreach (FileInfo objFile in objDir.GetFiles())
                    {
                        objFile.Attributes = emAttr;
                    }

                    foreach (DirectoryInfo objFolder in objDir.GetDirectories())
                    {
                        SetFolderAttr(objFolder, emAttr);
                    }
                }
            }
            catch { }
        }

        /// <summary>
        /// Retrieves the specified manifest resource stream from the executing assembly.
        /// </summary>
        /// <param name="name">Name of the resource to retrieve.</param>
        /// <returns>A stream that contains the resource.</returns>
        public static Stream GetResourceAsStream(string name)
        {
            return Assembly.GetExecutingAssembly().GetManifestResourceStream(name);
        }

        /// <summary>
        /// Retrieves the specified manifest resource stream from the executing assembly as a string.
        /// </summary>
        /// <param name="name">Name of the resource to retrieve.</param>
        /// <returns>The value of the specified manifest resource.</returns>
        public static string GetResource(string name)
        {
            using (StreamReader streamReader = new StreamReader(GetResourceAsStream(name)))
            {
                return streamReader.ReadToEnd();
            }
        }

        /// <summary>
        /// Retrieves the specified manifest resource stream from the executing assembly as a string, replacing the specified old value with the specified new value.
        /// </summary>
        /// <param name="name">Name of the resource to retrieve.</param>
        /// <param name="oldValue">A string to be replaced.</param>
        /// <param name="newValue">A string to replace all occurrences of oldValue.</param>
        /// <returns>The value of the specified manifest resource, with all instances of oldValue replaced with newValue.</returns>
        public static string GetResource(string name, string oldValue, string newValue)
        {
            string returnValue = GetResource(name);
            return returnValue.Replace(oldValue, newValue);
        }

        /// <summary>
        /// Writes a compiled resource to a file.
        /// </summary>
        /// <param name="resourceName">The name of the resource.</param>
        /// <param name="fileName">The name of the file to write to.</param>
        public static void WriteResourceToFile(string resourceName, string fileName)
        {
            using (FileStream fileStream = new FileStream(fileName, FileMode.Create))
            {
                using (Stream stream = Utility.GetResourceAsStream(resourceName))
                {
                    while (true)
                    {
                        int intValue = stream.ReadByte();
                        if (intValue >= 0)
                        {
                            byte byteValue = (byte)intValue;
                            fileStream.WriteByte(byteValue);
                        }
                        else
                        {
                            break;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Returns the query that should be used for retrieving the list of tables for the specified database.
        /// </summary>
        /// <param name="databaseName">The database to be queried for.</param>
        /// <returns>The query that should be used for retrieving the list of tables for the specified database.</returns>
        public static string GetTableQuery(string databaseName)
        {
            return GetResource("RobotCoder.Resources.TableQuery.sql", "#DatabaseName#", databaseName);
        }

        /// <summary>
        /// Returns the query that should be used for retrieving the list of columns for the specified table.
        /// </summary>
        /// <param name="databaseName">The table to be queried for.</param>
        /// <returns>The query that should be used for retrieving the list of columns for the specified table.</returns>
        public static string GetColumnQuery(string tableName)
        {
            return GetResource("RobotCoder.Resources.ColumnQuery.sql", "#TableName#", tableName);
        }

        /// <summary>
        /// Retrieves the specified manifest resource stream from the executing assembly as a string, replacing the specified old value with the specified new value.
        /// </summary>
        /// <param name="name">Name of the resource to retrieve.</param>
        /// <param name="databaseName">The name of the database to be used.</param>
        /// <param name="grantLoginName">The name of the user to be used.</param>
        /// <returns>The queries that should be used to create the specified database login.</returns>
        public static string GetUserQueries(string databaseName, string grantLoginName)
        {
            string returnValue = GetResource("RobotCoder.Resources.User.sql");
            returnValue = returnValue.Replace("#DatabaseName#", databaseName);
            returnValue = returnValue.Replace("#UserName#", grantLoginName);
            return returnValue;
        }

        /// <summary>
        /// Returns the query that should be used for retrieving the list of tables for the specified database.
        /// </summary>
        /// <param name="databaseName">The database to be queried for.</param>
        /// <returns>The query that should be used for retrieving the list of tables for the specified database.</returns>
        public static string Get(string databaseName)
        {
            return GetResource("RobotCoder.Resources.TableQuery.sql", "#DatabaseName#", databaseName);
        }

        /// <summary>
        /// Retrieves the foreign key information for the specified table.
        /// </summary>
        /// <param name="connection">The SqlConnection to be used when querying for the table information.</param>
        /// <param name="tableName">Name of the table that foreign keys should be checked for.</param>
        /// <returns>DataReader containing the foreign key information for the specified table.</returns>
        public static DataTable GetForeignKeyList(SqlConnection connection, string tableName)
        {
            SqlParameter parameter;

            using (SqlCommand command = new SqlCommand("sp_fkeys", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                parameter = new SqlParameter("@pktable_name", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "pktable_name", DataRowVersion.Current, DBNull.Value);
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@pktable_owner", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "pktable_owner", DataRowVersion.Current, DBNull.Value);
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@pktable_qualifier", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "pktable_qualifier", DataRowVersion.Current, DBNull.Value);
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@fktable_name", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "fktable_name", DataRowVersion.Current, tableName);
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@fktable_owner", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "fktable_owner", DataRowVersion.Current, DBNull.Value);
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@fktable_qualifier", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "fktable_qualifier", DataRowVersion.Current, DBNull.Value);
                command.Parameters.Add(parameter);

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                return dataTable;
            }
        }

        /// <summary>
        /// Retrieves the primary key information for the specified table.
        /// </summary>
        /// <param name="connection">The SqlConnection to be used when querying for the table information.</param>
        /// <param name="tableName">Name of the table that primary keys should be checked for.</param>
        /// <returns>DataReader containing the primary key information for the specified table.</returns>
        public static DataTable GetPrimaryKeyList(SqlConnection connection, string tableName)
        {
            SqlParameter parameter;

            using (SqlCommand command = new SqlCommand("sp_pkeys", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                parameter = new SqlParameter("@table_name", SqlDbType.NVarChar, 128, ParameterDirection.Input, false, 0, 0, "table_name", DataRowVersion.Current, tableName);
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@table_owner", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "table_owner", DataRowVersion.Current, DBNull.Value);
                command.Parameters.Add(parameter);
                parameter = new SqlParameter("@table_qualifier", SqlDbType.NVarChar, 128, ParameterDirection.Input, true, 0, 0, "table_qualifier", DataRowVersion.Current, DBNull.Value);
                command.Parameters.Add(parameter);

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                return dataTable;
            }
        }

        /// <summary>
        /// Creates a string containing the parameter declaration for a stored procedure based on the parameters passed in.
        /// </summary>
        /// <param name="column">Object that stores the information for the column the parameter represents.</param>
        /// <param name="checkForOutputParameter">Indicates if the created parameter should be checked to see if it should be created as an output parameter.</param>
        /// <returns>String containing parameter information of the specified column for a stored procedure.</returns>
        public static string CreateParameterString(Column column, bool checkForOutputParameter)
        {
            string parameter;

            switch (column.Type.ToLower())
            {
                case "binary":
                    parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
                    break;

                case "bigint":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "bit":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "char":
                    parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
                    break;

                case "datetime":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "decimal":
                    if (column.Scale.Length == 0)
                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";
                    else
                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ", " + column.Scale + ")";
                    break;

                case "float":
                    parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";
                    break;

                case "image":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "int":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "money":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "nchar":
                    parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
                    break;

                case "ntext":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "nvarchar":
                    parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
                    break;

                case "numeric":
                    if (column.Scale.Length == 0)
                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ")";
                    else
                        parameter = "@" + column.Name + " " + column.Type + "(" + column.Precision + ", " + column.Scale + ")";
                    break;

                case "real":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "smalldatetime":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "smallint":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "smallmoney":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "sql_variant":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "sysname":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "text":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "timestamp":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "tinyint":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "varbinary":
                    parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
                    break;

                case "varchar":
                    parameter = "@" + column.Name + " " + column.Type + "(" + GetParameterLength(column) + ")";
                    break;

                case "uniqueidentifier":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                case "xml":
                    parameter = "@" + column.Name + " " + column.Type;
                    break;

                default:  // Unknow data type
                    throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
            }

            // Return the new parameter string
            if (checkForOutputParameter && (column.IsIdentity || column.IsRowGuidCol))
            {
                return parameter + " OUTPUT";
            }
            else
            {
                return parameter;
            }
        }

        /// <summary>
        /// Creates the length portion of the specified column.
        /// </summary>
        /// <param name="column">Object that stores the information for the column the parameter represents.</param>
        /// <returns>String containing length information for the specific column.</returns>
        public static string GetParameterLength(Column column)
        {
            if (column.Length == "-1")
            {
                return "max";
            }

            return column.Length;
        }

        /// <summary>
        /// Creates a string for a method parameter representing the specified column.
        /// </summary>
        /// <param name="column">Object that stores the information for the column the parameter represents.</param>
        /// <returns>String containing parameter information of the specified column for a method call.</returns>
        public static string CreateMethodParameter(Column column)
        {
            return GetCsType(column) + " " + FormatCamel(column.Name);
        }

        /// <summary>
        /// Creates the name of the method to call on a SqlDataReader for the specified column.
        /// </summary>
        /// <param name="column">The column to retrieve data for.</param>
        /// <returns>The name of the method to call on a SqlDataReader for the specified column.</returns>
        public static string GetCsType(Column column)
        {
            switch (column.Type.ToLower())
            {
                case "binary":
                    return "byte[]";
                case "bigint":
                    return "long";
                case "bit":
                    return "bool";
                case "char":
                    return "string";
                case "datetime":
                    return "DateTime";
                case "decimal":
                    return "decimal";
                case "float":
                    return "float";
                case "image":
                    return "byte[]";
                case "int":
                    return "int";
                case "money":
                    return "decimal";
                case "nchar":
                    return "string";
                case "ntext":
                    return "string";
                case "nvarchar":
                    return "string";
                case "numeric":
                    return "decimal";
                case "real":
                    return "decimal";
                case "smalldatetime":
                    return "DateTime";
                case "smallint":
                    return "short";
                case "smallmoney":
                    return "float";
                case "sql_variant":
                    return "byte[]";
                case "sysname":
                    return "string";
                case "text":
                    return "string";
                case "timestamp":
                    return "DateTime";
                case "tinyint":
                    return "byte";
                case "varbinary":
                    return "byte[]";
                case "varchar":
                    return "string";
                case "uniqueidentifier":
                    return "Guid";
                case "xml":
                    return "string";
                default:  // Unknow data type
                    throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
            }
        }

        /// <summary>
        /// Creates the GetXxx method to use for the specified column.
        /// </summary>
        /// <param name="column">The column to retrieve data for.</param>
        /// <returns>The name of the method to call on a SqlDataReader for the specified column.</returns>
        public static string GetGetMethod(Column column)
        {
            switch (column.Type.ToLower())
            {
                case "binary":
                    return "GetBytes";
                case "bigint":
                    return "GetInt64";
                case "bit":
                    return "GetBoolean";
                case "char":
                    return "GetString";
                case "datetime":
                    return "GetDateTime";
                case "decimal":
                    return "GetDecimal";
                case "float":
                    return "GetFloat";
                case "image":
                    return "GetBytes";
                case "int":
                    return "GetInt32";
                case "money":
                    return "GetDecimal";
                case "nchar":
                    return "GetString";
                case "ntext":
                    return "GetString";
                case "nvarchar":
                    return "GetString";
                case "numeric":
                    return "GetDecimal";
                case "real":
                    return "GetDecimal";
                case "smalldatetime":
                    return "GetDateTime";
                case "smallint":
                    return "GetIn16";
                case "smallmoney":
                    return "GetFloat";
                case "sql_variant":
                    return "GetBytes";
                case "sysname":
                    return "GetString";
                case "text":
                    return "GetString";
                case "timestamp":
                    return "GetDateTime";
                case "tinyint":
                    return "GetByte";
                case "varbinary":
                    return "GetBytes";
                case "varchar":
                    return "GetString";
                case "uniqueidentifier":
                    return "GetGuid";
                case "xml":
                    return "GetString";
                default:  // Unknow data type
                    throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
            }
        }

        /// <summary>
        /// Creates a string for the default value of a column's data type.
        /// </summary>
        /// <param name="column">The column to get a default value for.</param>
        /// <returns>The default value for the column.</returns>
        public static string GetDefaultValue(Column column)
        {
            switch (column.Type.ToLower())
            {
                case "binary":
                    return "new byte[0]";
                case "bigint":
                    return "0";
                case "bit":
                    return "false";
                case "char":
                    return "String.Empty";
                case "datetime":
                    return "new DateTime(0)";
                case "decimal":
                    return "Decimal.Zero";
                case "float":
                    return "0.0F";
                case "image":
                    return "new byte[0]";
                case "int":
                    return "0";
                case "money":
                    return "Decimal.Zero";
                case "nchar":
                    return "String.Empty";
                case "ntext":
                    return "String.Empty";
                case "nvarchar":
                    return "\"\"";
                case "numeric":
                    return "Decimal.Zero";
                case "real":
                    return "Decimal.Zero";
                case "smalldatetime":
                    return "DateTime.Now";
                case "smallint":
                    return "0";
                case "smallmoney":
                    return "0.0F";
                case "sql_variant":
                    return "new byte[0]";
                case "sysname":
                    return "null";
                case "text":
                    return "null";
                case "timestamp":
                    return "DateTime.Now";
                case "tinyint":
                    return "0x00";
                case "varbinary":
                    return "new byte[0]";
                case "varchar":
                    return "null";
                case "uniqueidentifier":
                    return "Guid.Empty";
                case "xml":
                    return "null";
                default:  // Unknow data type
                    throw (new Exception("Invalid SQL Server data type specified: " + column.Type));
            }
        }

        /// <summary>
        /// Formats a string in Camel case (the first letter is in lower case).
        /// </summary>
        /// <param name="original">A string to be formatted.</param>
        /// <returns>A string in Camel case.</returns>
        public static string FormatCamel(string original)
        {
            if (original.Length > 0)
            {
                return Char.ToLower(original[0]) + original.Substring(1);
            }
            else
            {
                return String.Empty;
            }
        }

        /// <summary>
        /// Formats a string in Pascal case (the first letter is in upper case).
        /// </summary>
        /// <param name="original">A string to be formatted.</param>
        /// <returns>A string in Pascal case.</returns>
        public static string FormatPascal(string original)
        {
            if (original.Length > 0)
            {
                return Char.ToUpper(original[0]) + original.Substring(1);
            }
            else
            {
                return String.Empty;
            }
        }

        /// <summary>
        /// Formats the table name for use as a data transfer object.
        /// </summary>
        /// <param name="tableName">The name of the table to format.</param>
        /// <returns>The table name, formatted for use as a data transfer object.</returns>
        public static string FormatClassName(string tableName)
        {
            string className;

            if (Char.IsUpper(tableName[0]))
            {
                className = tableName;
            }
            else
            {
                className = FormatPascal(tableName);
            }

            // Attept to removing a trailing 's' or 'S', unless, the last two characters are both 's' or 'S'.
            if (className[className.Length - 1] == 'S' && className[className.Length - 2] != 'S')
            {
                className = className.Substring(0, className.Length - 1);
            }
            else if (className[className.Length - 1] == 's' && className[className.Length - 2] != 's')
            {
                className = className.Substring(0, className.Length - 1);
            }

            return className;
        }

        /// <summary>
        /// Formats the table name for use as a variable.
        /// </summary>
        /// <param name="tableName">The name of the table to format.</param>
        /// <returns>The table name, formatted for use as a variable.</returns>
        public static string FormatVariableName(string tableName)
        {
            string variableName;

            if (Char.IsLower(tableName[0]))
            {
                variableName = tableName;
            }
            else
            {
                variableName = FormatCamel(tableName);
            }

            // Attept to removing a trailing 's' or 'S', unless, the last two characters are both 's' or 'S'.
            if (variableName[variableName.Length - 1] == 'S' && variableName[variableName.Length - 2] != 'S')
            {
                variableName = variableName.Substring(0, variableName.Length - 1);
            }
            else if (variableName[variableName.Length - 1] == 's' && variableName[variableName.Length - 2] != 's')
            {
                variableName = variableName.Substring(0, variableName.Length - 1);
            }

            if (variableName == "bool"
                || variableName == "byte"
                || variableName == "char"
                || variableName == "decimal"
                || variableName == "double"
                || variableName == "float"
                || variableName == "long"
                || variableName == "object"
                || variableName == "sbyte"
                || variableName == "short"
                || variableName == "string"
                || variableName == "uint"
                || variableName == "ulong"
                || variableName == "ushort")
            {
                variableName += "Value";
            }

            return variableName;
        }

        /// <summary>
        /// Matches a SQL Server data type to a SqlClient.SqlDbType.
        /// </summary>
        /// <param name="sqlDbType">A string representing a SQL Server data type.</param>
        /// <returns>A string representing a SqlClient.SqlDbType.</returns>
        public static string GetSqlDbType(string sqlDbType)
        {
            switch (sqlDbType.ToLower())
            {
                case "binary":
                    return "Binary";
                case "bigint":
                    return "BigInt";
                case "bit":
                    return "Bit";
                case "char":
                    return "Char";
                case "datetime":
                    return "DateTime";
                case "decimal":
                    return "Decimal";
                case "float":
                    return "Float";
                case "image":
                    return "Image";
                case "int":
                    return "Int";
                case "money":
                    return "Money";
                case "nchar":
                    return "NChar";
                case "ntext":
                    return "NText";
                case "nvarchar":
                    return "NVarChar";
                case "numeric":
                    return "Decimal";
                case "real":
                    return "Real";
                case "smalldatetime":
                    return "SmallDateTime";
                case "smallint":
                    return "SmallInt";
                case "smallmoney":
                    return "SmallMoney";
                case "sql_variant":
                    return "Variant";
                case "sysname":
                    return "VarChar";
                case "text":
                    return "Text";
                case "timestamp":
                    return "Timestamp";
                case "tinyint":
                    return "TinyInt";
                case "varbinary":
                    return "VarBinary";
                case "varchar":
                    return "VarChar";
                case "uniqueidentifier":
                    return "UniqueIdentifier";
                case "xml":
                    return "Xml";
                default:  // Unknow data type
                    throw (new Exception("Invalid SQL Server data type specified: " + sqlDbType));
            }
        }

        /// <summary>
        /// Creates a string for a SqlParameter representing the specified column.
        /// </summary>
        /// <param name="column">Object that stores the information for the column the parameter represents.</param>
        /// <returns>String containing SqlParameter information of the specified column for a method call.</returns>
        public static string CreateSqlParameter(Table table, Column column)
        {
            string className = Utility.FormatClassName(table.Name);
            string variableName = Utility.FormatVariableName(className);
            string type = Utility.CreateMethodParameter(column).Split(' ')[0];

            if (column.Type == "xml")
            {
                return "new SqlParameter(\"@" + column.Name + "\", SqlDbType.Xml) { Value = " + variableName + "." + FormatPascal(column.Name) + " }";
            }
            else
            {
                if (type == "string")
                {
                    //return "new SqlParameter(\"@" + column.Name + "\", Null.GetNull(" + variableName + "." + FormatPascal(column.Name) + ", \"\"))";
                    return "Null.GetNull(" + variableName + "." + FormatPascal(column.Name) + ", \"\")";
                }
                else if (type == "bool")
                {
                    return "Null.GetNull(" + variableName + "." + FormatPascal(column.Name) + ", false)";
                }
                else
                {
                    //return "new SqlParameter(\"@" + column.Name + "\", Null.GetNull(" + variableName + "." + FormatPascal(column.Name) + ", DBNull.Value))";
                    return "Null.GetNull(" + variableName + "." + FormatPascal(column.Name) + ", DBNull.Value)";
                }
            }
        }

        public static string CreateSqlParameter2(Table table, Column column)
        {
            string className = Utility.FormatClassName(table.Name);
            string variableName = Utility.FormatVariableName(className);
            string type = Utility.CreateMethodParameter(column).Split(' ')[0];

            if (column.Type == "xml")
            {
                return "new SqlParameter(\"@" + column.Name + "\", SqlDbType.Xml) { Value = " + Utility.FormatCamel(column.Name) + " }";
            }
            else
            {
                if (type == "string")
                {
                    //return "new SqlParameter(\"@" + column.Name + "\", Null.GetNull(" + variableName + "." + FormatPascal(column.Name) + ", \"\"))";
                    return "Null.GetNull(" + Utility.FormatCamel(column.Name) + ", \"\")";
                }
                else if (type == "bool")
                {
                    return "Null.GetNull(" + Utility.FormatCamel(column.Name) + ", false)";
                }
                else
                {
                    //return "new SqlParameter(\"@" + column.Name + "\", Null.GetNull(" + variableName + "." + FormatPascal(column.Name) + ", DBNull.Value))";
                    return "Null.GetNull(" + Utility.FormatCamel(column.Name) + ", DBNull.Value)";
                }
            }
        }

        public static void CreateClass_ValidationUtility(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "clsValidationUtility.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {
                streamWriter.WriteLine("using System;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("namespace " + targetNamespace + "." + daoSuffix);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    /// <summary>");
                streamWriter.WriteLine("    /// Provides validation functionality for method arguments or property assignments.");
                streamWriter.WriteLine("    /// </summary>");
                streamWriter.WriteLine("    public abstract class clsValidationUtility");
                streamWriter.WriteLine("    {");
                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Validates that the <see cref=\"System.String\"/> value is not <code>null</code> or empty.");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"name\">The name of the argument.</param>");
                streamWriter.WriteLine("        /// <param name=\"value\">The value of the argument.</param>");
                streamWriter.WriteLine("        public static void ValidateArgument(string name, string value)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            ValidateArgument(name, value, true);");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Validates that the <see cref=\"System.String\"/> value is not <code>null</code> or empty.");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"name\">The name of the argument.</param>");
                streamWriter.WriteLine("        /// <param name=\"value\">The value of the argument.</param>");
                streamWriter.WriteLine("        /// <param name=\"checkLength\">Indicates if the value's length should be validated.</param>");
                streamWriter.WriteLine("        public static void ValidateArgument(string name, string value, bool checkLength)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            if (value == null)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                throw new ArgumentNullException(name);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            if (checkLength && value.Length == 0)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                string message = String.Format(\"{0} cannot be a zero-length string.\", name);");
                streamWriter.WriteLine("                throw new ArgumentException(message);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Validates that the <see cref=\"System.Object\"/> value is not <code>null</code>.");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"name\">The name of the argument.</param>");
                streamWriter.WriteLine("        /// <param name=\"value\">The value of the argument.</param>");
                streamWriter.WriteLine("        public static void ValidateArgument(string name, object value)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            if (value == null)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                throw new ArgumentNullException(name);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");


            }
        }

        public static void CreateClass_Null(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "Null.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {
                streamWriter.WriteLine("using System;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("namespace " + targetNamespace + "." + daoSuffix);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    public class Null");
                streamWriter.WriteLine("    {");
                streamWriter.WriteLine("        // define application encoded null values");
                streamWriter.WriteLine("        public static short NullShort");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return -1; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static int NullInteger");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return -1; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static byte NullByte");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return 255; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static float NullSingle");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return float.MinValue; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static double NullDouble");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return double.MinValue; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static decimal NullDecimal");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return decimal.MinValue; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static System.DateTime NullDate");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return System.DateTime.MinValue; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static string NullString");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return \"\"; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static bool NullBoolean");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return false; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static Guid NullGuid");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            get { return Guid.Empty; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        // sets a field to an application encoded null value ( used in BLL layer )");
                streamWriter.WriteLine("        public static object SetNull(object objValue, object objField)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            object functionReturnValue = null;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            if (System.Convert.IsDBNull(objValue))");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (objField is short)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullShort;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is byte)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullByte;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is int)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullInteger;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is float)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullSingle;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is double)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullDouble;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is decimal)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullDecimal;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is System.DateTime)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullDate;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is string)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullString;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is bool)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullBoolean;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is Guid)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = NullGuid;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    // complex object");
                streamWriter.WriteLine("                    functionReturnValue = null;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                // return value");
                streamWriter.WriteLine("                functionReturnValue = objValue;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return functionReturnValue;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        // sets a field to an application encoded null value ( used in BLL layer )");
                streamWriter.WriteLine("        public static object SetNull(System.Reflection.PropertyInfo objPropertyInfo)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            object functionReturnValue = null;");
                streamWriter.WriteLine("            switch (objPropertyInfo.PropertyType.ToString())");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                case \"System.Int16\":");
                streamWriter.WriteLine("                    functionReturnValue = NullShort;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.Int32\":");
                streamWriter.WriteLine("                case \"System.Int64\":");
                streamWriter.WriteLine("                    functionReturnValue = NullInteger;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"system.Byte\":");
                streamWriter.WriteLine("                    functionReturnValue = NullByte;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.Single\":");
                streamWriter.WriteLine("                    functionReturnValue = NullSingle;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.Double\":");
                streamWriter.WriteLine("                    functionReturnValue = NullDouble;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.Decimal\":");
                streamWriter.WriteLine("                    functionReturnValue = NullDecimal;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.DateTime\":");
                streamWriter.WriteLine("                    functionReturnValue = NullDate;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.String\":");
                streamWriter.WriteLine("                case \"System.Char\":");
                streamWriter.WriteLine("                    functionReturnValue = NullString;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.Boolean\":");
                streamWriter.WriteLine("                    functionReturnValue = NullBoolean;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                case \"System.Guid\":");
                streamWriter.WriteLine("                    functionReturnValue = NullGuid;");
                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                default:");
                streamWriter.WriteLine("                    // Enumerations default to the first entry");
                streamWriter.WriteLine("                    Type pType = objPropertyInfo.PropertyType;");
                streamWriter.WriteLine("                    if (pType.BaseType.Equals(typeof(System.Enum)))");
                streamWriter.WriteLine("                    {");
                streamWriter.WriteLine("                        System.Array objEnumValues = System.Enum.GetValues(pType);");
                streamWriter.WriteLine("                        Array.Sort(objEnumValues);");
                streamWriter.WriteLine("                        functionReturnValue = System.Enum.ToObject(pType, objEnumValues.GetValue(0));");
                streamWriter.WriteLine("                    }");
                streamWriter.WriteLine("                    else");
                streamWriter.WriteLine("                    {");
                streamWriter.WriteLine("                        // complex object");
                streamWriter.WriteLine("                        functionReturnValue = null;");
                streamWriter.WriteLine("                    }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                    break;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return functionReturnValue;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        // convert an application encoded null value to a database null value ( used in DAL )");
                streamWriter.WriteLine("        public static object GetNull(object objField, object objDBNull)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            object functionReturnValue = null;");
                streamWriter.WriteLine("            functionReturnValue = objField;");
                streamWriter.WriteLine("            if (objField == null)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                functionReturnValue = objDBNull;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is byte)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (Convert.ToByte(objField) == NullByte)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is short)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (Convert.ToInt16(objField) == NullShort)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is int)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (Convert.ToInt32(objField) == NullInteger)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is float)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (Convert.ToSingle(objField) == NullSingle)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is double)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (Convert.ToDouble(objField) == NullDouble)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is decimal)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (Convert.ToDecimal(objField) == NullDecimal)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is System.DateTime)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                // compare the Date part of the DateTime with the DatePart of the NullDate ( this avoids subtle time differences )");
                streamWriter.WriteLine("                if (Convert.ToDateTime(objField).Date == NullDate.Date)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is string)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (objField == null)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    if (objField.ToString() == NullString)");
                streamWriter.WriteLine("                    {");
                streamWriter.WriteLine("                        functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                    }");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is bool)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (Convert.ToBoolean(objField) == NullBoolean)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else if (objField is Guid)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (((System.Guid)objField).Equals(NullGuid))");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objDBNull;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return functionReturnValue;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        // checks if a field contains an application encoded null value");
                streamWriter.WriteLine("        public static bool IsNull(object objField)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool functionReturnValue = false;");
                streamWriter.WriteLine("            if ((objField != null))");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (objField is int)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullInteger);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is short)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullShort);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is byte)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullByte);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is float)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullSingle);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is double)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullDouble);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is decimal)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullDecimal);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is System.DateTime)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    DateTime objDate = (DateTime)objField;");
                streamWriter.WriteLine("                    functionReturnValue = objDate.Date.Equals(NullDate.Date);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is string)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullString);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is bool)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullBoolean);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else if (objField is Guid)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    functionReturnValue = objField.Equals(NullGuid);");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    // complex object");
                streamWriter.WriteLine("                    functionReturnValue = false;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                functionReturnValue = true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return functionReturnValue;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");
                streamWriter.WriteLine();

            }
        }

        public static void CreateClass_Untils(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "Untils.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {

                streamWriter.WriteLine("using System;");
                streamWriter.WriteLine("using System.Data;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("namespace " + targetNamespace + "." + daoSuffix);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    public sealed class Untils");
                streamWriter.WriteLine("    {");
                streamWriter.WriteLine("        //private static readonly string connectionString = ConfigurationManager.ConnectionStrings[\"System.Data.SqlClient\"].ConnectionString;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        #region Global Excute Database");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        ///");
                streamWriter.WriteLine("        /// Excute None Return Query");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"Script\">script value</param>");
                streamWriter.WriteLine("        /// <returns> error message if it's occur otherwise empty</returns>");
                streamWriter.WriteLine("        public static string ExecuteNonQuery(string Script)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, Script);");
                streamWriter.WriteLine("                Program.gobjDb.ExecuteNonQuery(CommandType.Text, Script);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return ex.Message;");
                streamWriter.WriteLine("            };");
                streamWriter.WriteLine("            return string.Empty;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Excute None Return store procedure");
                streamWriter.WriteLine("        ///");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"spName\">procedure name</param>");
                streamWriter.WriteLine("        /// <param name=\"parameterValues\">store procedure parameters</param>");
                streamWriter.WriteLine("        /// <returns>error message if it's occur otherwise empty</returns>");
                streamWriter.WriteLine("        public static string ExecuteNonQuery(string spName, params object[] parameterValues)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //SqlHelper.ExecuteNonQuery(connectionString, spName, parameterValues);");
                streamWriter.WriteLine("                Program.gobjDb.ExecuteNonQuery(spName, parameterValues);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return ex.Message;");
                streamWriter.WriteLine("            };");
                streamWriter.WriteLine("            return string.Empty;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Excute None Return store procedure");
                streamWriter.WriteLine("        ///");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"spName\">procedure name</param>");
                streamWriter.WriteLine("        /// <param name=\"parameterValues\">store procedure parameters</param>");
                streamWriter.WriteLine("        /// <returns>error message if it's occur otherwise empty</returns>");
                streamWriter.WriteLine("        public static string ExecuteNonQuery(ref System.Data.SqlClient.SqlTransaction Transaction, string spName, params object[] parameterValues)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //SqlHelper.ExecuteNonQuery(connectionString, spName, parameterValues);");
                streamWriter.WriteLine("                Program.gobjDb.ExecuteNonQuery(Transaction, spName, parameterValues);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return ex.Message;");
                streamWriter.WriteLine("            };");
                streamWriter.WriteLine("            return string.Empty;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        ///");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"SQL\"></param>");
                streamWriter.WriteLine("        /// <param name=\"sErr\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static IDataReader ExecuteReaderEx(string SQL, ref string sErr)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            sErr = string.Empty;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //return ((IDataReader)(SqlHelper.ExecuteReader(connectionString, CommandType.Text, SQL)));");
                streamWriter.WriteLine("                return ((IDataReader)(Program.gobjDb.ExecuteReader(CommandType.Text, SQL)));");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                sErr = ex.Message;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return null;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Run a return values query");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"SQL\">TSQL </param>");
                streamWriter.WriteLine("        /// <param name=\"sErr\">error message if it's occur otherwise empty</param>");
                streamWriter.WriteLine("        /// <returns>Datata set contain return value </returns>");
                streamWriter.WriteLine("        public static DataSet ExecuteReader(string SQL, ref string sErr)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            sErr = string.Empty;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //return ConvertDataReaderToDataSet(((IDataReader)(SqlHelper.ExecuteReader(connectionString, CommandType.Text, SQL))));");
                streamWriter.WriteLine("                return ConvertDataReaderToDataSet(((IDataReader)(Program.gobjDb.ExecuteReader(CommandType.Text, SQL))));");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                sErr = ex.Message;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return null;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Excute a store procedure");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"spName\">store procedure  name</param>");
                streamWriter.WriteLine("        /// <param name=\"sErr\">error message if it's occur otherwise empty</param>");
                streamWriter.WriteLine("        /// <param name=\"parameterValues\">store procedure parameters</param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static DataSet ExecuteReader(string spName, ref string sErr, params object[] parameterValues)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            sErr = string.Empty;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //return ConvertDataReaderToDataSet(((IDataReader)(SqlHelper.ExecuteReader(connectionString, spName, parameterValues))));");
                streamWriter.WriteLine("                return ConvertDataReaderToDataSet(((IDataReader)(Program.gobjDb.ExecuteReader(spName, parameterValues))));");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                sErr = ex.Message;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return null;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// return ireader");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"spName\"></param>");
                streamWriter.WriteLine("        /// <param name=\"sErr\"></param>");
                streamWriter.WriteLine("        /// <param name=\"parameterValues\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static IDataReader ExecuteReaderEx(string spName, ref string sErr, params object[] parameterValues)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            sErr = string.Empty;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //return ((IDataReader)(SqlHelper.ExecuteReader(connectionString, spName, parameterValues)));");
                streamWriter.WriteLine("                return ((IDataReader)(Program.gobjDb.ExecuteReader(spName, parameterValues)));");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                sErr = ex.Message;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return null;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        ///Excute a Scalar TSQL");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"SQL\">TSQL </param>");
                streamWriter.WriteLine("        /// <param name=\"sErr\">error message if it's occur otherwise empty </param>");
                streamWriter.WriteLine("        /// <returns>Return vale as object type</returns>");
                streamWriter.WriteLine("        public static object ExecuteScalar(string SQL, ref string sErr)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            sErr = string.Empty;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //return SqlHelper.ExecuteScalar(connectionString, CommandType.Text, SQL);");
                streamWriter.WriteLine("                return Program.gobjDb.ExecuteScalar(CommandType.Text, SQL);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                sErr = ex.Message;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return null;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Excute a Scalar store procedure");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"spName\">store procedure name</param>");
                streamWriter.WriteLine("        /// <param name=\"sErr\">error message if it's occur otherwise empty</param>");
                streamWriter.WriteLine("        /// <param name=\"parameterValues\">prammeters list/param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static object ExecuteScalar(string spName, ref string sErr, params object[] parameterValues)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            sErr = string.Empty;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //return SqlHelper.ExecuteScalar(connectionString, spName, parameterValues);");
                streamWriter.WriteLine("                return Program.gobjDb.ExecuteScalar(spName, parameterValues);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                sErr = ex.Message;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return null;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Excute a Scalar store procedure");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"spName\">store procedure name</param>");
                streamWriter.WriteLine("        /// <param name=\"sErr\">error message if it's occur otherwise empty</param>");
                streamWriter.WriteLine("        /// <param name=\"parameterValues\">prammeters list/param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static object ExecuteScalar(ref System.Data.SqlClient.SqlTransaction Transaction, string spName, ref string sErr, params object[] parameterValues)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            sErr = string.Empty;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //return SqlHelper.ExecuteScalar(connectionString, spName, parameterValues);");
                streamWriter.WriteLine("                return Program.gobjDb.ExecuteScalar(Transaction, spName, parameterValues);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                sErr = ex.Message;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return null;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        //--------------------------------///");
                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Convert IDataReader to dataset");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"reader\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static DataSet ConvertDataReaderToDataSet(IDataReader reader)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            DataSet objDataSet = new DataSet();");
                streamWriter.WriteLine("            do");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                objDataSet.Tables.Add(ConvertDataReaderToDataTable(reader, false));");
                streamWriter.WriteLine("            } while (reader.NextResult());");
                streamWriter.WriteLine("            reader.Close();");
                streamWriter.WriteLine("            return objDataSet;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Convert IDataReader to Data table");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"reader\"></param>");
                streamWriter.WriteLine("        /// <param name=\"bClose\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static DataTable ConvertDataReaderToDataTable(IDataReader reader, bool bClose)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            DataTable objDataTable = new DataTable();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            int intFieldCount = reader.FieldCount;");
                streamWriter.WriteLine("            int intCounter;");
                streamWriter.WriteLine("            for (intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            objDataTable.BeginLoadData();");
                streamWriter.WriteLine("            object[] objValues = new object[intFieldCount];");
                streamWriter.WriteLine("            while (reader.Read())");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                reader.GetValues(objValues);");
                streamWriter.WriteLine("                objDataTable.LoadDataRow(objValues, true);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            if (bClose)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                reader.Close();");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            objDataTable.EndLoadData();");
                streamWriter.WriteLine("            return objDataTable;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        #endregion Global Excute Database");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        #region \" dataset processing\"");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static string StoreToDataSetXML(DataSet ds)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string ret = string.Empty;");
                streamWriter.WriteLine("            System.IO.StringWriter sw = new System.IO.StringWriter();");
                streamWriter.WriteLine("            ds.WriteXml(sw, XmlWriteMode.WriteSchema);");
                streamWriter.WriteLine("            ret = sw.ToString();");
                streamWriter.WriteLine("            sw.Close();");
                streamWriter.WriteLine("            return ret;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public static DataSet GetDataSetFromXML(string sxml)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            DataSet ds = new DataSet();");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                System.IO.StringReader sr = new System.IO.StringReader(sxml);");
                streamWriter.WriteLine("                ds.ReadXml(sr);");
                streamWriter.WriteLine("                sr.Close();");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { }");
                streamWriter.WriteLine("            return ds;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        #endregion \" dataset processing\"");
                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");
                streamWriter.WriteLine();

            }
        }

        public static void CreateClass_Common(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "clsCommon.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {

                streamWriter.WriteLine("using System;");
                streamWriter.WriteLine("using System.Drawing;");
                streamWriter.WriteLine("using System.Windows.Forms;");
                streamWriter.WriteLine("using System.Text;");
                streamWriter.WriteLine("using System.Security.Cryptography;");
                streamWriter.WriteLine("using System.Collections.Generic;");
                streamWriter.WriteLine("using System.Data;");
                streamWriter.WriteLine("using System.Reflection;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("namespace " + targetNamespace);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    internal static class clsCommon");
                streamWriter.WriteLine("    {");
                streamWriter.WriteLine("        private const string mcstrClassName = \"frmDanhSachDuTuyen\";         //class name");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Show Error Message");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strModule\">Method/function name</param>");
                streamWriter.WriteLine("        /// <param name=\"strMsg\">Custom message to show</param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncMsgError(string strClass, string strModule, string strMsg)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                string strMsgShow = string.Format(\"Class : {0} \\nMethod : {1} \\nMessage : \\n{2}\", strClass, strModule, strMsg);");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                //MessageBox.Show(strMsgShow, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error);");
                streamWriter.WriteLine("                DevExpress.XtraEditors.XtraMessageBox.Show(strMsgShow, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error);");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { DevExpress.XtraEditors.XtraMessageBox.Show(ex.StackTrace, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error); }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Show ERROR message");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strMsg\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncMsgError(string strMsg)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //MessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error);");
                streamWriter.WriteLine("                DevExpress.XtraEditors.XtraMessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error);");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { DevExpress.XtraEditors.XtraMessageBox.Show(ex.StackTrace, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error); }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Show information message");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strMsg\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncMsgInfo(string strMsg)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //MessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Information);");
                streamWriter.WriteLine("                DevExpress.XtraEditors.XtraMessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Information);");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { DevExpress.XtraEditors.XtraMessageBox.Show(ex.StackTrace, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error); }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Show warning message");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strMsg\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncMsgWarning(string strMsg)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //MessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Warning);");
                streamWriter.WriteLine("                DevExpress.XtraEditors.XtraMessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Warning);");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { DevExpress.XtraEditors.XtraMessageBox.Show(ex.StackTrace, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error); }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Show confirm message");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strMsg\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncMsgConfirm(string strMsg)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //if (MessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) return true;");
                streamWriter.WriteLine("                if (DevExpress.XtraEditors.XtraMessageBox.Show(strMsg, clsConst.gcstrAppName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) return true;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return false;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { DevExpress.XtraEditors.XtraMessageBox.Show(ex.StackTrace, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error); }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Show open file dialog");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strFilter\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static string fncOpenFile(string strFilter)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strResult = \"\";");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                using (OpenFileDialog objOpen = new OpenFileDialog())");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    objOpen.Filter = strFilter;");
                streamWriter.WriteLine("                    if (objOpen.ShowDialog() == DialogResult.OK) strResult = objOpen.FileName;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { DevExpress.XtraEditors.XtraMessageBox.Show(ex.StackTrace, clsConst.gcstrAppName, MessageBoxButtons.OK, MessageBoxIcon.Error); }");
                streamWriter.WriteLine("            return strResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Kiem tra chuoi la null");
                streamWriter.WriteLine("        /// Author: QuyetLT");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strText\">Chuoi dau vao</param>");
                streamWriter.WriteLine("        /// <param name=\"strMsg\">Message thong bao</param>");
                streamWriter.WriteLine("        /// <returns>Bool</returns>");
                streamWriter.WriteLine("        public static bool fncIsBlank(string strText, string strMsg = null)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (DBNull.Value.Equals(strText)) return true;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                if (string.IsNullOrEmpty(strText))");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    //show message and set focus if available");
                streamWriter.WriteLine("                    if (strMsg != null) fncMsgWarning(strMsg);");
                streamWriter.WriteLine("                    blnResult = true;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { throw ex; }");
                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Convert gia tri sang DbDatetime");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"dtValue\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static object fncCnvToDbDatetime(DateTime dtValue)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            if (dtValue <= DateTime.MinValue || dtValue >= DateTime.MaxValue) return System.Data.SqlTypes.SqlDateTime.Null;");
                streamWriter.WriteLine("            return dtValue;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Convert gia tri sang DbString");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strInput\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static object fncCnvToDbString(string strInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            if (DBNull.Value.Equals(strInput)) return \"\";");
                streamWriter.WriteLine("            if (string.IsNullOrEmpty(strInput)) return \"\";");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            strInput = strInput.Replace(\"[\", \"[[]\");");
                streamWriter.WriteLine("            //strInput = \"'\" + strInput.Replace(\"'\", \"''\") + \"'\";");
                streamWriter.WriteLine("            strInput = strInput.Replace(\"'\", \"''\");");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return strInput;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Convert gia tri sang Dbbit");
                streamWriter.WriteLine("        /// Author: QuyetLT");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"blnInput\">Gia tri vao</param>");
                streamWriter.WriteLine("        /// <returns>1 hoac 0</returns>");
                streamWriter.WriteLine("        public static object fncCnvToDbBit(bool blnInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            if (blnInput) return 1;");
                streamWriter.WriteLine("            return 0;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Convert gia tri sang DbImage");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objImage\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static object fncCnvToDbImage(Image objImage)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            if (objImage == null) return \"\";");
                streamWriter.WriteLine("            return objImage;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang string");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>string</returns>");
                streamWriter.WriteLine("        public static string fncCnvNullToString(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strResult = \"\";");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //tra ve \"\" neu input la null");
                streamWriter.WriteLine("                if (Convert.IsDBNull(objInput)) return \"\";");
                streamWriter.WriteLine("                if (objInput == null) return \"\";");
                streamWriter.WriteLine("                strResult = Convert.ToString(objInput);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { return \"\"; }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return strResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang Guid");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>Guid</returns>");
                streamWriter.WriteLine("        public static Guid fncCnvNullToGuid(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            Guid objResult = Guid.Empty;");
                streamWriter.WriteLine();
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //tra ve emty neu input la null");
                streamWriter.WriteLine("                if (Convert.IsDBNull(objInput)) return objResult;");
                streamWriter.WriteLine("                if (objInput == null) return objResult;");
                streamWriter.WriteLine("                objResult = new Guid(clsCommon.fncCnvNullToString(objInput));");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { return objResult; }");
                streamWriter.WriteLine();
                streamWriter.WriteLine("            return objResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang byte[]");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>byte[]</returns>");
                streamWriter.WriteLine("        public static byte[] fncCnvNullToByteArray(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            byte[] strResult = null;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //tra ve \"\" neu input la null");
                streamWriter.WriteLine("                if (Convert.IsDBNull(objInput)) return strResult;");
                streamWriter.WriteLine("                if (objInput == null) return strResult;");
                streamWriter.WriteLine("                strResult = (byte[])(objInput);");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { return strResult; }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return strResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang int");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>int</returns>");
                streamWriter.WriteLine("        public static int fncCnvNullToInt(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strTemp = fncCnvNullToString(objInput);");
                streamWriter.WriteLine("            int intResult = 0;");
                streamWriter.WriteLine("            int.TryParse(strTemp, out intResult);");
                streamWriter.WriteLine("            return intResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang float");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>float</returns>");
                streamWriter.WriteLine("        public static float fncCnvNullToFloat(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strTemp = fncCnvNullToString(objInput);");
                streamWriter.WriteLine("            float fltResult = 0F;");
                streamWriter.WriteLine("            float.TryParse(strTemp, out fltResult);");
                streamWriter.WriteLine("            return fltResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang bool");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>bool</returns>");
                streamWriter.WriteLine("        public static bool fncCnvNullToBool(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strTemp = fncCnvNullToString(objInput).Trim();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            if (strTemp == \"1\") return true;");
                streamWriter.WriteLine("            if (strTemp == \"0\") return false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine("            bool.TryParse(strTemp, out blnResult);");
                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi bool sang int");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>bool</returns>");
                streamWriter.WriteLine("        public static int fncCnvBoolToInt(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strTemp = fncCnvNullToString(objInput).Trim().ToLower();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            if (strTemp == \"true\") return 1;");
                streamWriter.WriteLine("            if (strTemp == \"false\") return 0;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return 0;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang Byte");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>int</returns>");
                streamWriter.WriteLine("        public static byte fncCnvNullToByte(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strTemp = fncCnvNullToString(objInput);");
                streamWriter.WriteLine("            byte bytResult = 0;");
                streamWriter.WriteLine("            byte.TryParse(strTemp, out bytResult);");
                streamWriter.WriteLine("            return bytResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Chuyen doi sang Datetime");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"objInput\"></param>");
                streamWriter.WriteLine("        /// <returns>DateTime</returns>");
                streamWriter.WriteLine("        public static DateTime fncCnvNullToDatetime(object objInput)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strTemp = fncCnvNullToString(objInput);");
                streamWriter.WriteLine("            DateTime dtResult = DateTime.MinValue;");
                streamWriter.WriteLine("            DateTime.TryParse(strTemp, out dtResult);");
                streamWriter.WriteLine("            return dtResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// In hoa chữ cái đầu tiên");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strValue\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static string fncCapitalizeFirst(string strValue)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            System.Globalization.TextInfo ti = new System.Globalization.CultureInfo(\"en-US\", false).TextInfo;");
                streamWriter.WriteLine("            return ti.ToTitleCase(strValue);");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Trả về HọĐệm + Tên từ họ tên đầy đủ");
                streamWriter.WriteLine("        /// Author: QuyetLT");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strHoten\">Tên đầy đủ</param>");
                streamWriter.WriteLine("        /// <param name=\"strHoDem\">Trả về họ đệm</param>");
                streamWriter.WriteLine("        /// <param name=\"strTen\">Trả về tên</param>");
                streamWriter.WriteLine("        /// <returns>bool - true - thành công, false-thất bại</returns>");
                streamWriter.WriteLine("        public static bool fncGetHoTen(string strHoten, out string strHoDem, out string strTen)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            strHoDem = \"\";");
                streamWriter.WriteLine("            strTen = \"\";");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                strHoten = strHoten.Trim();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                int intIndex = strHoten.LastIndexOf(\" \");");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                if (intIndex <= 0)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    strHoDem = \"\";");
                streamWriter.WriteLine("                    strTen = strHoten;");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                else");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    strHoDem = strHoten.Substring(0, intIndex + 1).Trim();");
                streamWriter.WriteLine("                    strTen = strHoten.Substring(intIndex).Trim();");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                clsCommon.fncMsgError(mcstrClassName, \"fncGetData\", ex.Message);");
                streamWriter.WriteLine("                return false;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Xây dựng câu query like");
                streamWriter.WriteLine("        /// Author: QuyetLT");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"strField\">Tên trường</param>");
                streamWriter.WriteLine("        /// <param name=\"strQuery\">Dữ liệu query</param>");
                streamWriter.WriteLine("        /// <returns>string</returns>");
                streamWriter.WriteLine("        public static string fncBuildQueryLike(string strField, string strQuery)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strReturnValue = null;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            strReturnValue = \"\";");
                streamWriter.WriteLine("            string[] strArr = null;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //trim and remove double space");
                streamWriter.WriteLine("                strQuery = strQuery.Trim();");
                streamWriter.WriteLine("                strQuery = strQuery.Replace(\"  \", \"\");");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                //split into array");
                streamWriter.WriteLine("                strArr = strQuery.Split(new char[] { ' ' });");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                //start concat");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                for (int i = 0; i <= strArr.Length - 2; i++)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    //build to get string like : FIELDNAME Like '%abc%'");
                streamWriter.WriteLine("                    strReturnValue += \" \" + strField;");
                streamWriter.WriteLine("                    strReturnValue += \" LIKE \";");
                streamWriter.WriteLine("                    strReturnValue += \"'\";");
                streamWriter.WriteLine("                    strReturnValue += fncCnvToDbString(\"%\" + strArr[i] + \"%\");");
                streamWriter.WriteLine("                    strReturnValue += \"'\";");
                streamWriter.WriteLine("                    strReturnValue += \" AND\";");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                //concat with the last item");
                streamWriter.WriteLine("                strReturnValue += \" \" + strField;");
                streamWriter.WriteLine("                strReturnValue += \" LIKE \";");
                streamWriter.WriteLine("                strReturnValue += \"'\";");
                streamWriter.WriteLine("                strReturnValue += fncCnvToDbString(\"%\" + strArr[strArr.Length - 1] + \"%\");");
                streamWriter.WriteLine("                strReturnValue += \"'\";");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                clsCommon.fncMsgError(mcstrClassName, \"fncBuildQueryLike\", ex.Message);");
                streamWriter.WriteLine("                return \"\";");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            finally");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                strArr = null;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return strReturnValue;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Release COM object");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"obj\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncReleaseObject(object obj)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnReturnValue = false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);");
                streamWriter.WriteLine("                obj = null;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                obj = null;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            finally");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                GC.Collect();");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            return blnReturnValue;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Open database");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncOpenConnection()");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                Program.gobjConn.Open();");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { clsCommon.fncMsgError(\"Không thể kết nối với cơ sở dữ liệu. Xin hãy thử lại.\"); }");
                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Close database");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncCloseConnection()");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                Program.gobjConn.Close();");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { clsCommon.fncMsgError(\"Không thể kết nối với cơ sở dữ liệu. Xin hãy thử lại.\"); }");
                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// take any string and encrypt it using MD5 then");
                streamWriter.WriteLine("        /// return the encrypted data ");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"data\">input text you will enterd to encrypt it</param>");
                streamWriter.WriteLine("        /// <returns>return the encrypted text as hexadecimal string</returns>");
                streamWriter.WriteLine("        public static string fncGetMD5HashData(string data)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            //create new instance of md5");
                streamWriter.WriteLine("            MD5 md5 = MD5.Create();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            //convert the input text to array of bytes");
                streamWriter.WriteLine("            byte[] hashData = md5.ComputeHash(Encoding.Default.GetBytes(data));");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            //create new instance of StringBuilder to save hashed data");
                streamWriter.WriteLine("            StringBuilder returnValue = new StringBuilder();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            //loop for each byte and add it to StringBuilder");
                streamWriter.WriteLine("            for (int i = 0; i < hashData.Length; i++)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                returnValue.Append(hashData[i].ToString());");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            // return hexadecimal string");
                streamWriter.WriteLine("            return returnValue.ToString();");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// take any string and encrypt it using SHA1 then");
                streamWriter.WriteLine("        /// return the encrypted data");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"data\">input text you will enterd to encrypt it</param>");
                streamWriter.WriteLine("        /// <returns>return the encrypted text as hexadecimal string</returns>");
                streamWriter.WriteLine("        public static string fncGetSHA1HashData(string data)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                //create new instance of sha1");
                streamWriter.WriteLine("                SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                //convert the input text to array of bytes");
                streamWriter.WriteLine("                byte[] hashData = sha1.ComputeHash(Encoding.Default.GetBytes(data));");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                //create new instance of StringBuilder to save hashed data");
                streamWriter.WriteLine("                StringBuilder returnValue = new StringBuilder();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                //loop for each byte and add it to StringBuilder");
                streamWriter.WriteLine("                for (int i = 0; i < hashData.Length; i++)");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    returnValue.Append(hashData[i].ToString(\"x2\"));");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                // return hexadecimal string");
                streamWriter.WriteLine("                return returnValue.ToString();");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { return \"\"; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// encrypt input text using MD5 and compare it with");
                streamWriter.WriteLine("        /// the stored encrypted text");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"inputData\">input text you will enterd to encrypt it</param>");
                streamWriter.WriteLine("        /// <param name=\"storedHashData\">the encrypted text stored on file or database ... etc</param>");
                streamWriter.WriteLine("        /// <returns>true or false depending on input validation</returns>");
                streamWriter.WriteLine("        public static bool fncValidateMD5HashData(string inputData, string storedHashData)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            //hash input text and save it string variable");
                streamWriter.WriteLine("            string getHashInputData = fncGetMD5HashData(inputData);");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            if (string.Compare(getHashInputData, storedHashData) == 0)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return false;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();


                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// encrypt input text using SHA1 and compare it with");
                streamWriter.WriteLine("        /// the stored encrypted text");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"inputData\">input text you will enterd to encrypt it</param>");
                streamWriter.WriteLine("        /// <param name=\"storedHashData\">the encrypted text stored on file or database ... etc</param>");
                streamWriter.WriteLine("        /// <returns>true or false depending on input validation</returns>");
                streamWriter.WriteLine("        public static bool fncValidateSHA1HashData(string inputData, string storedHashData)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            //hash input text and save it string variable");
                streamWriter.WriteLine("            string getHashInputData = fncGetSHA1HashData(inputData);");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            if (string.Compare(getHashInputData, storedHashData) == 0)");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            else");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return false;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Tính số tháng giữa 2 khoảng thời gian");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"dtStart\"></param>");
                streamWriter.WriteLine("        /// <param name=\"dtEnd\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static int fncMonthDifference(DateTime dtStart, DateTime dtEnd)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                return Math.Abs((dtStart.Month - dtEnd.Month) + 12 * (dtStart.Year - dtEnd.Year));");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { return 0; }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Convert bitmap to byte[]");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"img\"></param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static byte[] fncCnvImageToByte(Image img)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            if (img == null) return null;");
                streamWriter.WriteLine("            ImageConverter converter = new ImageConverter();");
                streamWriter.WriteLine("            return (byte[])converter.ConvertTo(img, typeof(byte[]));");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();


                streamWriter.WriteLine("        public static string fncGetXepLoai(float fltDiemXepLoai)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            string strResult = \"\";");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (fltDiemXepLoai >= 9.0F) return \"Xuất sắc\";");
                streamWriter.WriteLine("                if (fltDiemXepLoai < 9.0F && fltDiemXepLoai >= 8.0F) return \"Giỏi\";");
                streamWriter.WriteLine("                if (fltDiemXepLoai < 8.0F && fltDiemXepLoai >= 7.0F) return \"Khá\";");
                streamWriter.WriteLine("                if (fltDiemXepLoai < 7.0F && fltDiemXepLoai >= 6.0F) return \"TB Khá\";");
                streamWriter.WriteLine("                if (fltDiemXepLoai < 6.0F && fltDiemXepLoai >= 5.0F) return \"Trung bình\";");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("            return strResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// Kiểm tra giá trị có tồn tại trong 1 datatable");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        /// <param name=\"tblSource\">datatable source</param>");
                streamWriter.WriteLine("        /// <param name=\"strField\">trường cần kiểm tra</param>");
                streamWriter.WriteLine("        /// <param name=\"strCriteria\">dữ liệu so sánh</param>");
                streamWriter.WriteLine("        /// <returns></returns>");
                streamWriter.WriteLine("        public static bool fncDataExist(DataTable tblSource, string strField, string strCriteria)");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            bool blnResult = false;");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                if (tblSource == null) return false;");
                streamWriter.WriteLine("                if (tblSource.Rows.Count <= 0) return false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                DataRow[] objRows = tblSource.Select(string.Format(\"{0} Like '{1}'\", strField, strCriteria));");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                if (objRows.Length <= 0) return false;");
                streamWriter.WriteLine();

                streamWriter.WriteLine("                return true;");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch (Exception ex) { clsCommon.fncMsgError(MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name, ex.Message); }");
                streamWriter.WriteLine("            return blnResult;");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine();

                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");

            }
        }

        public static void CreateClass_Const(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "clsConst.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {
                streamWriter.WriteLine("namespace " + targetNamespace);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    public static class clsConst");
                streamWriter.WriteLine("    {");
                streamWriter.WriteLine("        public const string gcstrAppName = \"App name here\";");
                streamWriter.WriteLine("        public const int gcintNoneValue = -1;");
                streamWriter.WriteLine("        public const string gcstrAddSuccess = \"Thêm mới thành công!\";");
                streamWriter.WriteLine("        public const string gcstrAddError = \"Thêm mới thất bại vui lòng thử lại!\";");
                streamWriter.WriteLine("        public const string gcstrDeleteNotChoose = \"Chưa có dữ liệu được chọn\";");
                streamWriter.WriteLine("        public const string gcstrDeleteConfirm = \"Bạn có chắc muốn xóa : \";");
                streamWriter.WriteLine("        public const string gcstrUpdateSuccess = \"Cập nhật thành công!\";");
                streamWriter.WriteLine("        public const string gcstrUpdateError = \"Cập nhật thất bại vui lòng thử lại!\";");
                streamWriter.WriteLine();

                streamWriter.WriteLine("        public const string gcstrTotalPageFormat = \"/{0}\";");
                streamWriter.WriteLine("        public const int gcintDefaultItemPerPage = 100;             //gia tri mac dinh so row hien thi tren datagridview");
                streamWriter.WriteLine("        public const string gcstrIsnotNumeric = \"Giá trị nhập vào không phải là số.\";");
                streamWriter.WriteLine();

                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");

            }
        }

        public static void CreateClass_Enum(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "clsEnum.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {
                streamWriter.WriteLine("namespace " + targetNamespace);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    public static class clsEnum");
                streamWriter.WriteLine("    {");

                streamWriter.WriteLine("        public enum FORM_MODE");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            ADD = 1,");
                streamWriter.WriteLine("            EDIT = 2,");
                streamWriter.WriteLine("            VIEW = 3,");
                streamWriter.WriteLine("            NONE = 4,");
                streamWriter.WriteLine("        }");

                streamWriter.WriteLine("        public enum GIOI_TINH");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            NAM = 1,");
                streamWriter.WriteLine("            NU = 0,");
                streamWriter.WriteLine("        }");

                streamWriter.WriteLine("        public enum NHOM_QUYEN");
                streamWriter.WriteLine("        { ");
                streamWriter.WriteLine("            ADMIN = 0,");
                streamWriter.WriteLine("            NHAP_LIEU = 1,");
                streamWriter.WriteLine("            BAO_CAO = 2,");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");

            }
        }

        public static void CreateClass_Events(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "clsEvents.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {

                streamWriter.WriteLine("using System;");
                streamWriter.WriteLine("using System.Collections.Generic;");
                streamWriter.WriteLine("using System.Text;");

                streamWriter.WriteLine("namespace " + targetNamespace);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    class clsEvents");
                streamWriter.WriteLine("    {");
                streamWriter.WriteLine("        public delegate void dgaMasterUpdateHandler();");
                streamWriter.WriteLine("        public delegate void dgaRoleUpdated();");

                streamWriter.WriteLine("        public event dgaMasterUpdateHandler evnMasterUpdate;");
                streamWriter.WriteLine("        public event dgaRoleUpdated evnRoleUpdated;");

                streamWriter.WriteLine("        public void RaiseMasterUpdate()");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            evnMasterUpdate();");
                streamWriter.WriteLine("        }");

                streamWriter.WriteLine("        public void RaiseRoleUpdated()");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            evnRoleUpdated();");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");

            }
        }

        public static void CreateClass_Program(string path, string targetNamespace, string daoSuffix)
        {
            string fileName = Path.Combine(path, "Program.cs");
            using (StreamWriter streamWriter = new StreamWriter(fileName, true))
            {

                streamWriter.WriteLine("using System;");
                streamWriter.WriteLine("using System.Collections.Generic;");
                streamWriter.WriteLine("using System.Data.Common;");
                streamWriter.WriteLine("using System.Windows.Forms;");
                streamWriter.WriteLine("using DevExpress.LookAndFeel;");
                streamWriter.WriteLine("using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;");
                streamWriter.WriteLine("using Microsoft.Practices.EnterpriseLibrary.Data;");

                streamWriter.WriteLine("namespace " + targetNamespace);
                streamWriter.WriteLine("{");
                streamWriter.WriteLine("    internal static class Program");
                streamWriter.WriteLine("    {");
                streamWriter.WriteLine("        public static Database gobjDb = null;");
                streamWriter.WriteLine("        public static DbConnection gobjConn = null;");
                streamWriter.WriteLine("        public static DbTransaction gobjTrans = null;");
                streamWriter.WriteLine("        public static bool gblnLoggedIn = false;");
                streamWriter.WriteLine("        public static string gstrCurrentUser = \"\";");
                streamWriter.WriteLine("        public static clsEvents gevnApplication;");
                streamWriter.WriteLine("        public static List<string> glstRoles;");

                streamWriter.WriteLine("        /// <summary>");
                streamWriter.WriteLine("        /// The main entry point for the application.");
                streamWriter.WriteLine("        /// </summary>");
                streamWriter.WriteLine("        [STAThread]");
                streamWriter.WriteLine("        private static void Main()");
                streamWriter.WriteLine("        {");
                streamWriter.WriteLine("            try");
                streamWriter.WriteLine("            {");
                streamWriter.WriteLine("                Application.EnableVisualStyles();");
                streamWriter.WriteLine("                Application.SetCompatibleTextRenderingDefault(false);");

                streamWriter.WriteLine("                DevExpress.Skins.SkinManager.EnableFormSkins();");
                streamWriter.WriteLine("                DevExpress.UserSkins.BonusSkins.Register();");
                streamWriter.WriteLine("                //UserLookAndFeel.Default.SetSkinStyle(\"DevExpress Style\");");
                streamWriter.WriteLine("                //UserLookAndFeel.Default.SetSkinStyle(\"Office 2010 Silver\");");
                streamWriter.WriteLine("                UserLookAndFeel.Default.SetSkinStyle(\"Office 2010 Blue\");");
                streamWriter.WriteLine("                //UserLookAndFeel.Default.SetSkinStyle(\"Metropolis\");");
                streamWriter.WriteLine("                //UserLookAndFeel.Default.SetSkinStyle(\"Whiteprint\");");

                streamWriter.WriteLine("                gobjDb = EnterpriseLibraryContainer.Current.GetInstance<Database>(\"" + targetNamespace + ".Properties.Settings.QLTHConnectionString\");");
                streamWriter.WriteLine("                gobjConn = gobjDb.CreateConnection();");
                streamWriter.WriteLine("                gevnApplication = new clsEvents();");
                streamWriter.WriteLine("                glstRoles = new List<string>();");

                streamWriter.WriteLine("                try");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    gobjConn.Open();");
                streamWriter.WriteLine("                }");
                streamWriter.WriteLine("                catch");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    clsCommon.fncMsgError(\"Không thể kết nối được đến cơ sở dữ liệu. Xin hãy kiểm tra lại.\");");
                streamWriter.WriteLine("                    using (frmConnectionSetting objSetting = new frmConnectionSetting())");
                streamWriter.WriteLine("                    {");
                streamWriter.WriteLine("                        objSetting.ShowDialog();");
                streamWriter.WriteLine("                        if (!objSetting.SettingSaved) return;");
                streamWriter.WriteLine("                    }");
                streamWriter.WriteLine("                }");


                streamWriter.WriteLine("                using (frmLogin objLogin = new frmLogin())");
                streamWriter.WriteLine("                {");
                streamWriter.WriteLine("                    objLogin.ShowDialog();");
                streamWriter.WriteLine("                    if (!objLogin.LoginSuccess)");
                streamWriter.WriteLine("                    {");
                streamWriter.WriteLine("                        gobjConn.Close();");
                streamWriter.WriteLine("                        return;");
                streamWriter.WriteLine("                    }");
                streamWriter.WriteLine("                }");

                streamWriter.WriteLine("                Application.Run(new frmMain());");

                streamWriter.WriteLine("                gobjConn.Close();");
                streamWriter.WriteLine("            }");
                streamWriter.WriteLine("            catch { clsCommon.fncMsgError(\"Xảy ra lỗi không mong muốn. Chương trình sẽ bị đóng.\"); }");
                streamWriter.WriteLine("        }");
                streamWriter.WriteLine("    }");
                streamWriter.WriteLine("}");

            }
        }

    }
}